﻿
CREATE PROCEDURE [dbo].[proc_Customer_GetItem]
	(
		@CustomerId uniqueidentifier
	)
AS
Begin
	Select
	Id,
	CompanyId,
	CustomerId,
	CustomerName,
	Project,
	ISNull(Webaddress,'') As Webaddress,
	Tel,
--(Select Top 1 Tel From(
--Select Mobile As Tel,IsMain,PersonTypeId,'A' as sort
--From ContactPerson
--Where CustomerId=@CustomerId
--And Mobile<>'' And (Mobile Is Not Null)
--Union All
--Select OfficeTel As Tel,IsMain,PersonTypeId,'B' as sort
--From ContactPerson
--Where CustomerId=@CustomerId
--And OfficeTel<>'' And (OfficeTel Is Not Null)
--Union All
--Select HomeTel As Tel,IsMain,PersonTypeId,'C' as sort
--From ContactPerson
--Where CustomerId=@CustomerId
--And HomeTel<>'' And (HomeTel Is Not Null)
--) a Order By IsMain Desc,PersonTypeId,sort) As Tel,
	Fax,
	Address,
	Zip,
	IsNull(IndustryOneId,0) As IndustryOneId,
	IsNull(IndustryTwoId,0) As IndustryTwoId,
	IsNull(SourceId,0) As SourceId,
	IsNull(StateId,0) As StateId,
	IsNull(TypeId,0) As TypeId,
	CreateName,
	ISNULL(CreateDate,'1900-01-01') As CreateDate,
	IsNull(ProtectId,1) As ProtectId,
	IsNull(Pid,1) As Pid,
	IsNull(Cid,1) As Cid,
	IsNull(Rid,1) As Rid,
	IntentionsId,
	PublicityDemandId,
	NetworkConsciousnessId,
	MnemonicWorks,
	ISNULL(ProtectDate,'1900-01-01') As ProtectDate,
	ProtectName,
	(Select Title From Customer_Industry Where Id=Customer.IndustryOneId) As IndustryOne,
	IsNull((Select Top 1 Title From Customer_Industry Where Id=Customer.IndustryTwoId),'...') As IndustryTwo,
	IsNull((Select Top 1 Title From Customer_Source Where Id=Customer.SourceId),'...') As Source,
	IsNull((Select Top 1 Title From Customer_State Where Id=Customer.StateId),'...') As State,
	IsNull((Select Top 1 Title From Customer_Type Where Id=Customer.TypeId),'...') As Type,
	IsNull((Select Top 1 Title From Customer_Protect Where Id=Customer.ProtectId),'...') As Protect,
	IsNull((Select Top 1 CompanyName From Company Where Id=Customer.CompanyId),'...') As Company,
	IsNull((Select Top 1 Title From Province Where Id=Customer.pid),'...') As Province,
	IsNull((Select Top 1 Title From City Where [Level]=2 And Id=Customer.Cid),'...') As City,
	IsNull((Select Top 1 Title From City Where [Level]=3 And Id=Customer.Rid),'...') As Region,
	IsNull((Select Top 1 Title From Customer_Intentions Where Id=Customer.IntentionsId),'...') As Intentions,
	IsNull((Select Top 1 Title From Customer_PublicityDemand Where Id=Customer.PublicityDemandId),'...') As PublicityDemand,
	IsNull((Select Top 1 Title From Customer_NetworkConsciousness Where Id=Customer.NetworkConsciousnessId),'...') As NetworkConsciousness,
	IsNull((Select Top 1 EName From Employee Where UserName=Customer.CreateName And UserName<>''),'...') As CreateNameEName,
	IsNull((Select Top 1 EName From Employee Where UserName=Customer.ProtectName And UserName<>''),'...') As ProtectNameEName,
	SalesPerson,
	IsNull((Select EName From Employee Where UserName=Customer.SalesPerson And UserName<>''),'...') As SalesPersonEName,
	ServicePerson,
	IsNull(ServicePersonEName,'...') ServicePersonEName,
	--ISNULL((Select EName From Employee Where UserName=Customer.ServicePerson And UserName<>''),'...') As ServicePersonEName,
--	(select count(o.id) from [order] o where o.CusId =Customer.CustomerId) as OrdersCount,
	OrdersCount,
	delFlag,
	Audit,
	ISNULL((Select Top 1 AuditDate From Customer_Auditlog Where CustomerId=Customer.CustomerId Order By AuditDate Desc),'1900-01-01') As AuditDate,
	IsNull((Select Top 1 EName From Employee Where UserName In(Select Top 1 AuditUser From Customer_Auditlog Where CustomerId=Customer.CustomerId Order By AuditDate Desc)),'...') As AuditUser
	,ExtractDate
	,IsNull(BDServicePerson,'') BDServicePerson
	,IsNull(BDServicePersonEName,'...') as BDServicePersonEName
	,RegTime
	,RegCapital
	,Employees
	,(Select Title From Customer_Employees where Id=Customer.Employees) EmployeesName
	,Introduction
	From Customer
	Where CustomerId=@CustomerId
End